package dao.mysql.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import dao.IProductDao;
import dataModel.Product;

public class ProductDao implements IProductDao {

	final String INSERT_PRODUCT = "insert into product (hashId, title, href, highPrice, lowPrice, rating, commentNum, commentUrl) values (?, ?, ?, ?, ?, ?, ?, ?);";

	final String SELECT_ALL_PRODUCTS = "select * from product order by id";

	final String UPDATE_COMMENT_URL = "update product set commentUrl = ? where title = ?";

	@Override
	public void insert(Product item) {
		try {
			Connection conn = Configuration.getConnection();
			PreparedStatement stmt = conn.prepareStatement(INSERT_PRODUCT);

			String id = item.getHashId();
			String title = item.getTitle();
			String href = item.getHref();
			Float highPrice = item.getHighPrice();
			Float lowPrice = item.getLowPrice();
			Float rating = item.getRating();
			Integer commentNum = item.getCommentNum();
			String commentUrl = item.getCommentUrl();

			id = id != null ? id : "";
			title = title != null ? title : "";
			href = href != null ? href : "";
			highPrice = highPrice != null ? highPrice : 0;
			lowPrice = lowPrice != null ? lowPrice : 0;
			rating = rating != null ? rating : 0;
			commentNum = commentNum != null ? commentNum : 0;
			commentUrl = commentUrl != null ? commentUrl : "";

			stmt.setString(1, id);
			stmt.setString(2, title);
			stmt.setString(3, href);
			stmt.setFloat(4, highPrice);
			stmt.setFloat(5, lowPrice);
			stmt.setFloat(6, rating);
			stmt.setInt(7, commentNum);
			stmt.setString(8, commentUrl);

			stmt.executeUpdate();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public void updateCommentUrl(String title, String url) {
		try {
			Connection conn = Configuration.getConnection();
			PreparedStatement stmt = conn.prepareStatement(UPDATE_COMMENT_URL);

			stmt.setString(1, url);
			stmt.setString(2, title);

			stmt.executeUpdate();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public void selectAll(List<Product> products) {
		try {
			Connection conn = Configuration.getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(SELECT_ALL_PRODUCTS);

			while (rs.next()) {
				Product prod = new Product();
				prod.setId(rs.getInt("id"));
				prod.setTitle(rs.getString("title"));
				prod.setHref(rs.getString("href"));
				prod.setRating(rs.getFloat("rating"));
				prod.setHighPrice(rs.getFloat("highPrice"));
				prod.setLowPrice(rs.getFloat("lowPrice"));
				prod.setCommentNum(rs.getInt("commentNum"));
				prod.setCommentUrl(rs.getString("commentUrl"));
				products.add(prod);
			}

			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
